<?php

class Database
{

	private static $instance;
	private static $queries = array();
	private static $verbose = true;
	private $connection;
	private $database;

	private function __construct()
	{
		$this->connection = @mysql_connect(DB_HOST, DB_USER, DB_PASS) or self::error();
		$this->database = @mysql_select_db(DB_NAME) or self::error();
	}

	/**
	 * Enter description here...
	 *
	 * @param bool $value
	 */
	public static function setVerbose($verbose = true)
	{
		self::$verbose = $verbose;
	}

	private static function error()
	{
		$exception = mysql_error();
		if(self::$verbose)
		{
			$queries = self::getAllQueries();

			foreach($queries as $query)
			{
				$exception .= self::formatQuery($query, false);
			}
		}
		throw new Exception('<pre>'.$exception.'</pre>');
	}

	/**
	 * Enter description here...
	 *
	 * @return Database
	 */
	public static function getInstance()
	{
		if(null == self::$instance)
		{
			self::$instance = new self();
		}
		return self::$instance;
	}

	/**
	 * Enter description here...
	 *
	 * @param string $query
	 * @return resource
	 */
	public static function query($query)
	{
		self::getInstance();
		//array_unshift(self::$queries,  self::formatQuery($query));
		self::$queries = array(self::formatQuery($query));
		$result = @mysql_query($query) or self::error();
		return $result;
	}

	public static function convertResult($result)
	{
		$rows = array();
		while($row = @mysql_fetch_array($result))
		{
			$rows[] = $row;
		}
		return $rows;
	}
	
	public static function getQueryArray($query)
	{
		$r = self::query($query);
		return self::convertResult($result, true);
	}

	/**
	 * Enter description here...
	 *
	 * @param string $table
	 * @param array $where
	 * @param string $orderBy
	 * @param string $dir
	 * @param int|string $limit
	 * @param bool $asArray
	 * @return array
	 */
	public static function select_rows($table, $where = array(), $orderBy = null, $dir = 'ASC', $limit = 0)
	{

		$q = 'SELECT * FROM `'.($table). '`';



		// Create where string if set
		if(count($where))
		{
			// Has loop started yet?
			$q .= 'WHERE '. self::createConditional($where);
		}

		// Add order if set
		if($orderBy)
		{
			$q .= ' ORDER BY `' . self::escape_string($orderBy) . '`';
			if($dir)
			{
				$q .= ' '.$dir;
			}
		}

		// Add limit if set
		if(0 < $limit)
		{
			$q .= ' LIMIT '.$limit;
		}
	
		$result = self::query($q);
		$rows = array();
		while($row = @mysql_fetch_array($result))
		{
			$rows[] = $row;
		}
		return $rows;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param string $table
	 * @param array $where
	 * @param string $orderBy
	 * @param string $dir
	 * @param int|string $limit
	 * @param bool $asArray
	 * @return array
	 */
	public static function select_distinct($table, $field, $where = array(), $orderBy = null, $dir = 'ASC', $limit = 0)
	{

		$q = 'SELECT DISTINCT(`'.$table. '`.`'.$field.'`)
				FROM `'.($table). '`';



		// Create where string if set
		if(count($where))
		{
			// Has loop started yet?
			$q .= 'WHERE '. self::createConditional($where);
		}

		// Add order if set
		if($orderBy)
		{
			$q .= ' ORDER BY `' . self::escape_string($orderBy) . '`';
			if($dir)
			{
				$q .= ' '.$dir;
			}
		}

		// Add limit if set
		if(0 < $limit)
		{
			$q .= ' LIMIT '.$limit;
		}
	
		$result = self::query($q);
		$rows = array();
		while($row = @mysql_fetch_array($result))
		{
			$rows[] = $row;
		}
		return $rows;
	}

	public static function select_count($table, array $where = array(), $field = 'id')
	{

		$q = '	SELECT COUNT(`' . $field . '`) as numItems
				FROM `' . $table . '`';

		// Create where string if set
		if(count($where))
		{
			// Has loop started yet?
			$looped = false;
			foreach($where as $field => $value)
			{
				if(!is_numeric($field))
				{
					if(true == $looped)
					// Add 'AND' if loop has started before
					{
						$q .= ' AND ';
					}
					else
					// Add 'WHERE' if this is the first time the loop is called
					{
						$q .= ' WHERE ';
					}
					$q .= "`" .$field . "`" . ' = ' . "'" . self::escape_string($value) . "'";
					$looped = true;
				}
			}
		}
		$q .= ' LIMIT 1';
		$result = self::query($q);
		$rows = self::convertResult($result);
		return $rows[0]['numItems'];
	}

	/**
	 * LEFT JOIN person ON person.id = team_players.personId
	 *
	 * @param string $table
	 * @param array $joins
	 * @param array $where
	 * @param string $orderBy
	 * @param string $dir
	 * @param int $limit
	 */
	public static function select_joined_rows($table, array $select = array(), array $joins = array(), array $where = array(), $orderBy = null, $dir = 'ASC', $limit = 0)
	{
		
		$q = 'SELECT `'.$table.'`.* FROM `'.$table. '`';
		if(count($joins))
		{
			foreach($joins as $join)
			{
				if(count($join) != 2)
				{
					throw new Exception('Mailformed JOIN');
				}
				$tables = array_keys($join);
				$q .= ' LEFT JOIN `'.$tables[0].'` ON `'.$tables[0].'`.`'.$join[$tables[0]].'` = `'.$tables[1].'`.`'.$join[$tables[1]].'`';
			}
		}

		// Create where string if set
		if(count($where))
		{
			// Has loop started yet?
			$looped = false;
			foreach($where as $field => $value)
			{
				if(!is_numeric($field))
				{
					if(true == $looped)
					// Add 'AND' if loop has started before
					{
						$q .= ' AND ';
					}
					else
					// Add 'WHERE' if this is the first time the loop is called
					{
						$q .= ' WHERE ';
					}
					$q .= "`" .$field . "`" . ' = ' . "'" . self::escape_string($value) . "'";
					$looped = true;
				}
			}
		}

		// Add order if set
		if($orderBy)
		{
			$q .= ' ORDER BY `' . self::escape_string($orderBy) . '`';
			if($dir)
			{
				$q .= ' '.$dir;
			}
		}

		// Add limit if set
		if(0 < $limit)
		{
			$q .= ' LIMIT '.$limit;
		}
		$result = self::query($q);
		$rows = array();
		while($row = @mysql_fetch_array($result))
		{
			$rows[] = $row;
		}
		return $rows;
	}



	/**
	 * Enter description here...
	 *
	 * @param string $table
	 * @param int $id
	 * @return array
	 */
	public static function select_row_by_id($table, $id)
	{
		$rows = self::select_rows($table, array('id' => $id), null, null, 1);
		if(count($rows))
		{
			return $rows[0];
		}
		return null;
	}

	/**
	 * Enter description here...
	 *
	 * @param string $table
	 * @param string $name
	 * @return array
	 */
	public static function select_row_by_name($table, $name)
	{
		$rows = self::select_rows($table, array('name' => $name), null, null, 1);
		if(0 < count($rows))
		{
			return $rows[0];
		}
		else
		{
			return null;
		}
	}
	
	public static function select_rows_by_query($query)
	{
		$result = self::query($query);
		return self::convertResult($result);
	}

	/**
	 * Enter description here...
	 *
	 * @param string $table
	 * @param array $data
	 * @return int	Id of the inserted row
	 */
	public static function insert_row($table, array $data)
	{
		$fields = array_keys($data);
		$insertFields = array();
		foreach($fields as $field)
		{
			if(!is_numeric($field))
			{
				$insertFields[] = $field;
			}			
		}
		$fieldString = implode('`,`', $insertFields);
		$fieldString = '`'.$fieldString.'`';
		// escape all values
		
		foreach($data as $key => $val)
		{
			unset($data[$key]);
			if(!is_numeric($key))
			{
				$data[$key] = self::escape_string($val);
			}
		}
		
		
		$valString = implode("','", $data);
		$valString = "'".$valString."'";

		$q = 'INSERT INTO ' . $table . ' (' . $fieldString . ')
					VALUES (' . $valString . ')';
		if(count($insertFields) != count($data))
		{
			die("Field count does not match value count\n".$q);
		}
		Database::query($q);
		return mysql_insert_id();
	}

	/**
	 * Enter description here...
	 *
	 * @param string $table
	 * @param int $id
	 * @param array $data
	 */
	public static function update_row($table, $id, array $data)
	{
		$q = 'UPDATE ' . $table . '  SET ';
		$loop = false;
		foreach($data as $field=>$value)
		{
			if(!is_numeric($field))
			{
				if(true == $loop)
				{
					$q .= ', ';
				}
				$q .= "`" . $field . "` =  '" . self::escape_string($value) . "'";
				$loop = true;
			}
		}
		$q .=  " WHERE id = '" . $id . "'";

		Database::query($q);
	}
	/**
	 * Enter description here...
	 *
	 * @param string $table
	 * @param int $id
	 * @param array $data
	 */
	public static function update_rows($table, array $data, array $where = array())
	{
		$q = 'UPDATE ' . $table . '  SET ';
		$loop = false;
		foreach($data as $field=>$value)
		{
			if(true == $loop)
			{
				$q .= ', ';
			}
			$q .= "`" . $field . "` =  '" . self::escape_string($value) . "'";
			$loop = true;
		}
		// Create where string if set
		if(count($where))
		{
			// Has loop started yet?
			$looped = false;
			foreach($where as $field => $value)
			{
				if(true == $looped)
				// Add 'AND' if loop has started before
				{
					$q .= ' AND ';
				}
				else
				// Add 'WHERE' if this is the first time the loop is called
				{
					$q .= ' WHERE ';
				}
				$q .= "`" .$field . "`" . ' = ' . "'" . self::escape_string($value) . "'";
				$looped = true;
			}
		}

		Database::query($q);
	}

	/**
	 * Enter description here...
	 *
	 * @param string $table
	 * @param int $id
	 * @param array $data
	 */
	public static function delete_row($table, $id)
	{
		$q = 'DELETE FROM ' . $table;
		$q .=  " WHERE id = '" . $id . "'";
		$q .=  " LIMIT 1";

		Database::query($q);
	}

	/**
	 * 
	 * @param string $table
	 * @param array $where
	 * @return int affected rows
	 */
	public static function delete_rows($table, array $where = array())
	{
		$q = 'DELETE FROM ' . $table;

		// Create where string if set
		if(count($where))
		{
			// Has loop started yet?
			$looped = false;
			foreach($where as $field => $value)
			{
				if(true == $looped)
				// Add 'AND' if loop has started before
				{
					$q .= ' AND ';
				}
				else
				// Add 'WHERE' if this is the first time the loop is called
				{
					$q .= ' WHERE ';
				}
				$q .= "`" .$field . "`" . ' = ' . "'" . self::escape_string($value) . "'";
				$looped = true;
			}
		}

		self::query($q);
		return self::getAffectedRows();
	}

	/**
	 * Enter description here...
	 *
	 * @param string $table
	 * @param string $name
	 * @param array $data
	 */
	public static function update_row_by_name($table, $name, array $data)
	{
		$q = 'UPDATE ' . $table . '  SET ';
		$loop = false;
		foreach($data as $field=>$value)
		{
			if(true == $loop)
			{
				$q .= ', ';
			}
			$q .= $field . " =  '" . self::escape_string($value) . "'";
			$loop = true;
		}
		$q .=  " WHERE name = '" . self::escape_string($name) . "'";
		Database::query($q);
	}
	
	/**
	 * 
	 * @param string $table
	 * @param int $id
	 * @param string $field
	 */
	public static function select_age_in_days($table, $id, $field)
	{
		$query = 'SELECT DATEDIFF(CURDATE(),  `'.$table.'`. `'.$field.'`) as age
					FROM `'.$table.'`
					WHERE `'.$table.'`. `id` = '.$id.'
					LIMIT 1';
		$rows = self::select_rows_by_query($query);
		return $rows[0]['age'];
	}
	
	/**
	 * 
	 * Enter description here ...
	 * @param string $table
	 */
	public static function truncate_table($table)
	{
		$q = 'TRUNCATE TABLE `'.self::escape_string($table).'`';
		Database::query($q);		
	}
	
	/**
	 * 
	 * @param string
	 */
	public static function delete_from_table($table)
	{
		$q = 'DELETE FROM `'.self::escape_string($table).'`';
		Database::query($q);		
		$q = 'ALTER TABLE `'.self::escape_string($table).'` AUTO_INCREMENT=1';
		Database::query($q);		
	}

	/**
	 * 
	 * Enter description here ...
	 * @param array $tables
	 */
	public static function truncate_tables(array $tables)
	{		
		foreach($tables as $table)
		{
			Database::truncate_table($table);
		}		
	}

	public static function getAllQueries()
	{
		
		return self::$queries;
	}

	/**
	 * Get last performed SQL query
	 *
	 * @return string
	 */
	public static function getLastQuery($html = false)
	{
		self::getInstance();
		return self::formatQuery(self::$queries[ 0], $html);
	}
	
	/**
	 * 
	 * Enter description here ...
	 * @param array $fields
	 * @param string $condition
	 */
	public static function createConditional($fields, $conditional = 'and')
	{
		if(count($fields))
		{			
			$conditions = array();
			foreach($fields as $field => $value)
			{
				if(!is_numeric($field))
				{
					// recurse by using "or => array(a =>1, b =>1)" or "and => array(a =>1, b =>2)"
					if(is_array($value) && (strtolower($field) == 'and' || strtolower($field) == 'or'))
					{
						$q[]= ' ('.self::createConditional($value, $field).')';
					}
					else 
					{
						$q[]=  " `" .$field . "`" . ' = ' . "'" . self::escape_string($value) . "'";
					}		
				}		
			}
			$q = implode(' '.strtoupper($conditional).' ', $q);
		}
		return $q;
	}

	protected static function createJoins(array $joins = array())
	{
		if(count($joins))
		{
			foreach($joins as $join)
			{
				if(count($join) != 2)
				{
					throw new Exception('Malformed JOIN');
				}
				$tables = array_keys($join);
				$q .= ' LEFT JOIN `'.$tables[0].'` ON `'.$tables[0].'`.`'.$join[$tables[0]].'` = `'.$tables[1].'`.`'.$join[$tables[1]].'`';
			}
		}
	}

	/**
	 * Enter description here...
	 *
	 * @param string $query
	 * @param bool $html
	 * @return string
	 */
	public static function formatQuery($query, $html = false)
	{
		$keywords = array('SELECT','FROM', 'LIMIT', 'ORDER BY', 'UPDATE', 'SET', 'WHERE', 'INSERT INTO', 'VALUES', 'AND', 'LEFT JOIN', 'RIGHT JOIN','ASC', 'DESC');

		foreach ($keywords as $keyword)
		{

			if(true == $html)
			{
				$query = str_replace($keyword, "<br />\n<span style=\"color: #FF0033;\">".$keyword . '</span>', $query);
				$query = ereg_replace("['][^']{0,}[']","<span style=\"color:#199819\">\\0</span>", $query);
				$query = ereg_replace("[`][^`]{0,}[`]","<span style=\"color:#6600AA\">\\0</span>", $query);
			}
			else
			{
				$query = str_replace($keyword, "\n".$keyword, $query);
			}
			if(true == $html)
			{
				$query = '<p>'.$query.'</p>';
			}
		}
		return $query;
	}
	
	/**
	 * return int
	 */
	public static function getAffectedRows()
	{
		$oInstance = self::getInstance();
		return mysql_affected_rows($oInstance->connection);
	}

	/**
	 * 
	 * Enter description here ...
	 * @param string $table
	 */
	public static function getTableStructure($table)
	{
		$query = 'DESCRIBE '.$table;
		$result = self::query($query);
		$fields = array();
		
		while($row = @mysql_fetch_array($result))
		{
			$tempType = $row['Type'];
			$row['Type'] = str_replace(' unsigned', '', $row['Type']);
			$fields[$row['Field']] = $row;
		}
		return $fields;
	}
	
	/**
	 * 
	 * Enter description here ...
	 * @return array
	 */
	public static function getTables()
	{
		$query = 'SHOW TABLES FROM `'.DB_NAME.'`';
		$result = self::query($query);
		$tables = array();
		while($row = @mysql_fetch_array($result))
		{
			$tables[] = $row[0];
		}
		return $tables;
	}
	
	/**
	 * Used instead of mysql_real_escape_string()
	 * @param string value
	 * @return string
	 */
	public static function escape_string($value)
	{
		// there must be a connection to use mysql_real_escape_string()
		self::getInstance();
		if(is_object($value))
			echo '<hr />'.print_r($value, true). '<hr />';
		return mysql_real_escape_string($value);
	}

	public function __destruct()
	{
		mysql_close($this->connection);
	}
}